Create a SQL Server CE Database From SQL Schema File

Introduction

In this article, I will explain how to create a SQL Server CE .sdf database file from a database schema defined in .sql scripts. In one project recently, I've automatically generated the .sql scripts from the logic model, and because there is no tool (at least, I don't know) for generating the SQL Server CE .sdf database file from these SQL scripts. Then, I decided to create a tool whose input is SQL scripts with the database schema and whose output is the .sdf database file.

Getting started with the solution

Let's open the Visual Studio.NET 2008 IDE and create a solution named create_sqlcedb. You have to create a reference to the System.Data.SqlServerCe assembly (see Figure 1).

Figure 1

Now let's add the SqlCEDatabaseCreator class and its ISqlCEDatabaseCreator interface to implement the underlying business logic of the application. The interface is very simple. It comprises two methods: one to create the database file and the other to get and set the command separator between the SQL commands in the database schema. Traditionally, this separator is the GO string, but the component is flexible to receive a set of define command separators (see Listing 1).

Listing 1

public interface ISqlCEDatabaseCreator
{
    void Create(string strCEDatabaseFilePath, string strSqlScriptsFilePath);
    string CommandSeparator { get; set; }
}

Now the SQLCEDatabaseCreator class realizes this interface (see Listing 2).

Listing 2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlServerCe;

namespace Olamendy.Data.Utils.SqlCEDatabaseCreatorPkg
{
    public class SqlCEDatabaseCreator : ISqlCEDatabaseCreator
    {
        #region Private attributes
        private string m_strCommandSeparator = "go";
        #endregion
        #region Private methods

        private void prvCreateDatabaseSchema(string strDatabaseSchema, string strConnString)
        {
            using (SqlCeConnection objConn = new SqlCeConnection(strConnString))
            {
                string[] arrCommands = strDatabaseSchema.Split(new string[] { this.m_strCommandSeparator }, StringSplitOptions.RemoveEmptyEntries);

                SqlCeCommand objCmd = new SqlCeCommand();
                objCmd.Connection = objConn;
                objConn.Open();
                foreach (string strCmd in arrCommands)
                {
                    string strTrimmedCmd = strCmd.Trim();
                    if (!String.IsNullOrEmpty(strTrimmedCmd))
                    {
                        objCmd.CommandText = strTrimmedCmd;
                        objCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        #endregion
        #region ISqlCEDatabaseCreator Members

        public void Create(string strCEDatabaseFilePath, string strSqlScriptsFilePath)
        {
            if (System.IO.File.Exists(strCEDatabaseFilePath))
            {
                string strMessage = String.Format("SQL CE database file {0} already exists",strCEDatabaseFilePath);
                throw new Exception(strMessage);
            }
            string strDatabaseSchema = File.ReadAllText(strSqlScriptsFilePath);
            string strConnString = "Data Source=" + strCEDatabaseFilePath;

            using (SqlCeEngine objCeEngine = new SqlCeEngine(strConnString))
            {
                objCeEngine.CreateDatabase();
                this.prvCreateDatabaseSchema(strDatabaseSchema, strConnString);
            }
        }

        public string CommandSeparator
        {
            get
            {
                return this.m_strCommandSeparator;
            }
            set
            {
                this.m_strCommandSeparator = value;
            }
        }
        #endregion
    }
}

The first step is to check if the database file already exists and throw an exception and read the SQL commands to create the database from the SQL script file. If the database file does not exist, we'll create it. In this case, the connection string specifies the file path and an instance of the class SqlCeEngine is responsible for this task (see Listing 3).

Listing 3

if (System.IO.File.Exists(strCEDatabaseFilePath))
{
    string strMessage = String.Format("SQL CE database file {0} already exists",strCEDatabaseFilePath);

    throw new Exception(strMessage);
}

string strDatabaseSchema = File.ReadAllText(strSqlScriptsFilePath); 
string strConnString = "Data Source=" + strCEDatabaseFilePath;
using (SqlCeEngine objCeEngine = new SqlCeEngine(strConnString))

{
    objCeEngine.CreateDatabase();
    this.prvCreateDatabaseSchema(strDatabaseSchema, strConnString);
}

After that, the prvCreateDatabaseSchema method is called by passing the declaration of the database schema and the connection string to the created database. Then, we split the string containing the commands to create the database schema into an array of simple commands to be executed (see Listing 4).

Listing 4

string[] arrCommands = strDatabaseSchema.Split(new string[] { this.m_strCommandSeparator }, StringSplitOptions.RemoveEmptyEntries);
SqlCeCommand objCmd = new SqlCeCommand();

After that, we open a connection to the created database and bind to the instance of the SqlCeCommand class in order to execute every command in the arrCommands array of strings (see Listing 5).

Listing 5

objCmd.Connection = objConn;
objConn.Open();

And finally, every command is sent to the database through the established connection. Before we send the commands, we need to trim every white space as well as avoid every empty string (see Listing 6).

Listing 6

foreach (string strCmd in arrCommands)
{
    string strTrimmedCmd = strCmd.Trim();
    if (!String.IsNullOrEmpty(strTrimmedCmd))
    {
        objCmd.CommandText = strTrimmedCmd;
        objCmd.ExecuteNonQuery();
    }
}

And this is a very useful tool to generate the .sdf database file for SQL Server CE.

Conclusion

In this article, I've explained how to create a SQL Server CE .sdf database file from a database schema defined in .sql scripts. Now you can apply this tool to solve your own business problems.